package com.zhf.dao.impl;

import com.zhf.dao.CourseContentDao;
import com.zhf.pojo.Course;
import com.zhf.pojo.Course_Lesson;
import com.zhf.pojo.Course_Section;
import com.zhf.utils.DateUtils;
import com.zhf.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @Author: ZhaoHuanfeng
 * @Date: 2020/10/31 16:11
 */

/**
 * 课程内容管理Dao层实现类
 */
public class CourseContentDaoImpl implements CourseContentDao {
    /**
     * 根据课程id查询课程相关信息
     *
     * @param courseId
     * @return
     */
    @Override
    public List<Course_Section> findSectionAndLessonByCourseId(int courseId) {

        try {
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

            //编写SQL
            String sql = "SELECT\n" +
                    "\tid,\n" +
                    "\tcourse_id,\n" +
                    "\tsection_name,\n" +
                    "\tdescription,\n" +
                    "\torder_num,\n" +
                    "\tSTATUS\n" +
                    "FROM course_section WHERE course_id=?";

            //执行SQL
            List<Course_Section> sectionList = queryRunner.query(sql, new BeanListHandler<Course_Section>(Course_Section.class), courseId);

            //根据章节ID查询课时信息
            for (Course_Section section : sectionList) {
                //调用方法逐个查询
                List<Course_Lesson> lessonList = findLessonBySectionId(section.getId());
                //将课时信息添加到章节信息中
                section.setLessonList(lessonList);
            }
            return sectionList;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }

    }

    /**
     * 根据章节ID查询章节相关的课时信息
     *
     * @param sectionId
     * @return
     */
    @Override
    public List<Course_Lesson> findLessonBySectionId(int sectionId) {

        try {
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

            String sql = "SELECT\n" +
                    "\tid,\n" +
                    "\tcourse_id,\n" +
                    "\tsection_id,\n" +
                    "\ttheme,\n" +
                    "\tduration,\n" +
                    "\tis_free,\n" +
                    "\torder_num,\n" +
                    "\tSTATUS\n" +
                    "FROM course_lesson WHERE section_id=?";

            List<Course_Lesson> lessonList = queryRunner.query(sql, new BeanListHandler<Course_Lesson>(Course_Lesson.class), sectionId);
            return lessonList;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 根据课程id 回显课程信息
     *
     * @param courseId
     * @return
     */
    @Override
    public Course findCourseByID(int courseId) {

        try {
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

            String sql = "SELECT id,course_name FROM course WHERE id=?";

            Course course = queryRunner.query(sql, new BeanHandler<Course>(Course.class), courseId);
            return course;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 保存章节信息
     *
     * @param section
     * @return
     */
    @Override
    public int saveSection(Course_Section section) {

        try {
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

            String sql = "INSERT INTO course_section(\n" +
                    "\tcourse_id,\n" +
                    "\tsection_name,\n" +
                    "\tdescription,\n" +
                    "\torder_num,\n" +
                    "\tSTATUS,\n" +
                    "\tcreate_time,\n" +
                    "\tupdate_time\n" +
                    ") VALUES(?,?,?,?,?,?,?)";

            Object[] param = {section.getCourse_id(), section.getSection_name(), section.getDescription(), section.getOrder_num(), section.getStatus(), section.getCreate_time(), section.getUpdate_time()};

            int row = queryRunner.update(sql, param);
            return row;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }

    /**
     * 修改章节
     *
     * @param section
     * @return
     */
    @Override
    public int updateSection(Course_Section section) {
        try {
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            String sql = "UPDATE course_section SET\n" +
                    "\tsection_name=?,\n" +
                    "\tdescription=?,\n" +
                    "\torder_num=?,\n" +
                    "\tupdate_time=?\n" +
                    "WHERE id=?";

            Object[] param = {section.getSection_name(), section.getDescription(), section.getOrder_num(), section.getUpdate_time(), section.getId()};

            int row = queryRunner.update(sql, param);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    /**
     * 修改章节状态
     *
     * @param id
     * @param status
     * @return
     */
    @Override
    public int updateSectionStatus(int id, int status) {
        try {
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

            String sql = "UPDATE course_section SET STATUS=?,update_time=? WHERE id=?";
            Object[] param = {status, DateUtils.getDateFormart(), id};

            int row = queryRunner.update(sql, param);

            return row;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    /**
     * 保存课时信息
     *
     * @param lesson
     * @return
     */
    @Override
    public int saveLesson(Course_Lesson lesson) {
        try {
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            String sql = "INSERT INTO course_lesson(\n" +
                    "\tcourse_id,\n" +
                    "\tsection_id,\n" +
                    "\ttheme,\n" +
                    "\tduration,\n" +
                    "\tis_free,\n" +
                    "\torder_num,\n" +
                    "\tcreate_time,\n" +
                    "\tupdate_time\n" +
                    ") VALUES(?,?,?,?,?,?,?,?)";

            Object[] param = {lesson.getCourse_id(), lesson.getSection_id(), lesson.getTheme(), lesson.getDuration(), lesson.getIs_free(), lesson.getOrderNum(), lesson.getCreate_time(), lesson.getUpdate_time()};

            int row = queryRunner.update(sql, param);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    /**
     * 修改课时信息
     *
     * @param lesson
     * @return
     */
    @Override
    public int updateLesson(Course_Lesson lesson) {
        try {
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            String sql = "UPDATE course_lesson SET\n" +
                    "\tcourse_id=?,\n" +
                    "\tsection_id=?,\n" +
                    "\ttheme=?,\n" +
                    "\tduration=?,\n" +
                    "\tis_free=?,\n" +
                    "\torder_num=?,\n" +
                    "\tupdate_time=?\n" +
                    "WHERE id=?";

            Object[] param = {lesson.getCourse_id(), lesson.getSection_id(), lesson.getTheme(), lesson.getDuration(), lesson.getIs_free(), lesson.getOrderNum(), lesson.getUpdate_time(), lesson.getId()};

            int row = queryRunner.update(sql, param);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }
}
